package com.example.mybatisdemo.mapper;

import com.example.mybatisdemo.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * Description:持久层接口
 * Date: 2024-08-01
 * Time: 11:54
 */
@Mapper
public interface UserInfoMapper {
    //查询所有用户
    @Select("select username,password,age,gender,phone from userinfo")
    public List<UserInfo> queryAllUser();

    //根据 age 查询
    @Select("select username,password,age,gender,phone from userinfo where age = #{age}")
    public List<UserInfo> queryByAge(Integer age);

    //根据姓名查询
    @Select("select id,username,password,age,gender,phone from userinfo where username = #{name}")
    public List<UserInfo> queryByName(@Param("name") String name);

    @Select("select id,username,password,age,gender,phone from userinfo")
    public List<UserInfo> query();

    @Insert("insert into userinfo(username,password,age,gender,phone) " +
            "values(#{username},#{password},#{age},#{gender},#{phone})")
    public Integer insert(UserInfo userInfo);

    //根据 id 删除数据
    @Delete("delete from userinfo where id = #{id}")
    public Integer delete(Integer id);

    @Select("select delete_flag,create_time,update_time from userinfo")
    public List<UserInfo> select();

    @Select("select id, username, `password`, age, gender, phone, delete_flag, \n" +
            "create_time, update_time from userinfo")
    public List<UserInfo> queryAllUser1();

    @Select("select id, username, `password`, age, gender, phone, delete_flag, " +
            "create_time, update_time from userinfo")
    @Results({
            @Result(column = "delete_flag",property = "deleteFlag"),
            @Result(column = "create_time",property = "createTime"),
            @Result(column = "update_time",property = "updateTime")
    })
    public List<UserInfo> queryAllUser2();

    @Select("select username,password,age,gender,phone from userinfo where id = #{id}")
    UserInfo queryById(Integer id);

    @Select("select password from userinfo where username = #{name}")
    UserInfo queryByName1(String name);

    @Select("select password from userinfo where username = '${name}'")
    List<UserInfo> queryByName2(String name);

    @Select("select id,username,age,gender,phone,delete_flag,create_time,update_time " +
            "from userinfo order by id #{sort}")
    List<UserInfo> queryAllUserBySort1(String sort); //传入排序方式:排升序 or 降序

    @Select("select id,username,age,gender,phone,delete_flag,create_time,update_time " +
            "from userinfo order by id ${sort}")
    List<UserInfo> queryAllUserBySort2(String sort); //传入排序方式:排升序 or 降序

    @Select("select id,username,age,gender,phone,delete_flag,create_time,update_time " +
            "from userinfo where username like concat('%',#{key},'%')")
    List<UserInfo> queryAllUserByLike(String key);
}


